package DBModule;

import java.io.IOException;

import java.sql.*;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.TreeMap;
import java.util.Map.Entry;
import java.util.TreeSet;

import Test.Course;
import Test.Exception.FactoryException;
import User.User;


final public class CourseTable {	
	protected static final DBUpdater<Course> inserter = new DBUpdater<Course>() {
		@Override
		public void update(Connection conn, Course... opt) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("insert into Course(ID, name) values(?, ?)");
				for (Course c : opt){
					ps.setInt(1, c.getId());
					ps.setString(2, c.getName());
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	protected static final DBUpdater<Course> updater = new DBUpdater<Course>() {
		@Override
		public void update(Connection conn, Course... opt) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement("update Course set name=? where ID=?");
				for (Course c : opt){
					ps.setString(1, c.getName());
					ps.setInt(2, c.getId());
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	protected static final DBUpdater<Course> deleter = new DBUpdater<Course>() {
		@Override
		public void update(Connection conn, Course... opt) throws SQLException,
				IOException {
			PreparedStatement ps = null;
			try{
				ps = conn.prepareStatement("delete from Course where ID=?");
				for (Course c : opt){
					ps.setInt(1, c.getId());
					ps.executeUpdate();
				}
			}
			finally{
				if (ps != null) ps.close();
			}
		}
	};
	
	/**
	 * keys[0] : CourseID
	 */
	protected static final DBQuerier<Course> querier = new DBQuerier<Course>() {
		@Override
		public Collection<Course> query(Connection conn, Object... keys)
				throws SQLException, IOException{
			int id = (Integer)keys[0];	// 解包
			ArrayList<Course> ret = new ArrayList<Course>();
			PreparedStatement ps = null;			
			try{
				ps = conn.prepareStatement("select name from Course where ID=?");
				ps.setInt(1, id);
				ResultSet rs = ps.executeQuery();
				while (rs.next())
					ret.add(new Course(id, rs.getString(1)));
			}
			finally{
				if (ps != null) ps.close();
			}
			return ret;
		}
	};
	
	protected static AbstractMap<Integer, Course> courseMap = new TreeMap<Integer, Course>();
	static{
		// 把数据库所有条目插入表
		DBQuerier<Course> all = new DBQuerier<Course>() {
			@Override
			public Collection<Course> query(Connection conn, Object... keys)
					throws SQLException, IOException {
				PreparedStatement ps = null;
				ArrayList<Course> ret = new ArrayList<Course>();
				try{
					ps = conn.prepareStatement("select ID, name from Course");
					ResultSet rs = ps.executeQuery();
					
					while (rs.next())
						ret.add(new Course(rs.getInt(1), rs.getString(2)));
				}
				finally{
					if (ps != null) ps.close();
				}
				
				return ret;
			}
		};
		Iterator<Course> iter = null;
		try{
			iter = DBManager.query(all).iterator();
			while (iter.hasNext()){
				Course c = iter.next();
				courseMap.put(c.getId(), c);
			}
		}
		catch (Exception exp){
			exp.printStackTrace();
		}
	}
	
	/**
	 * 得到ID为id的科目
	 * @param id	科目ID
	 * @return		对应科目，没找到返回null
	 */
	public static Course getCourse(int id){
		// 在内存查，查到直接返回
		Course ret = courseMap.get(id);
		if (ret != null) return ret;
		
		// 在数据库查
		Iterator<Course> rs = null;
		try{
			rs = DBManager.query(querier, id).iterator();
		}catch(Exception e){
			return null;
		}
		
		// 没查到返回ErrorCourse
		if (rs == null || !rs.hasNext()) return null;		
		// 把查到的结果插入表
		courseMap.put(id, ret = rs.next());
		return ret;
	}
	
	/**
	 * 得到所有已有科目
	 */
	public static Collection<Course> getAllCourse(){
		ArrayList<Course> arr = new ArrayList<Course>();
		Iterator<Course> iter = courseMap.values().iterator();
		while (iter.hasNext())
			arr.add(iter.next());
		
		return arr;
	}
	
	public static void main(String[] args){
		
		Course[] opt = {
			new Course(1, "Hello World"),
			new Course(2, "C++"),
		};
//		DBManager.update(inserter, opt);			
//		DBManager.update(updater, opt);
//		DBManager.update(deleter, opt);
		
		Iterator<Entry<Integer, Course>> iter = courseMap.entrySet().iterator();
		while (iter.hasNext()){
			Entry<Integer, Course> en = iter.next();
			System.out.printf("%d %s %d\n", en.getKey(), en.getValue(), en.getKey());
		}
	}
}

















